package com.proxy.autoclick.checker.dao;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.proxy.autoclick.common.util.Constants;
import com.proxy.autoclick.common.util.ConfigureString;
import com.proxy.autoclick.common.util.DataSourceUtil;
import com.proxy.autoclick.common.valueobject.Proxy;

/**
 * Proxy data access object with db
 *
 */
public class ProxyDao {
	
	/**
	 * Get data from proxies table with is_checked = b'0'
	 * b'0' = false; b'1' = true
	 * @return
	 * @throws SQLException
	 */
	public List<Proxy> getValidProxyList() throws SQLException
	{
        try {
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            List<Proxy> myProxyList = (List<Proxy>) qr.query( 
            		"select proxy from proxies where is_valided = b'1'", 
            		new BeanListHandler(Proxy.class));
            
            return myProxyList;
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
	}
	
	public List<Proxy> getProxyList(int startIndex, int size) throws SQLException
	{
        try {
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            List<Proxy> myProxyList = (List<Proxy>) qr.query( 
            		"select proxy from proxies where is_valided is null limit ?,?", 
            		new BeanListHandler(Proxy.class),
            		startIndex,size);
            
            return myProxyList;
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
	}
	
	/**
	 * Update is_check=b'1' to proxies table from the proxy parameter object.
	 * @param myProxy
	 * @throws SQLException
	 */
	public void updateProxy(Proxy myProxy) throws SQLException
	{
        try {
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            String isValided = myProxy.isValidForGoogleHost()?"b'1'":"b'0'";
            qr.update("UPDATE proxies SET is_valided = " + isValided + " where proxy = ?", 
            			myProxy.getProxy());
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
	}
	
	public void deleteProxyIfInvalid(Proxy myProxy) throws SQLException
	{
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		if(!myProxy.isValidForGoogleHost())
		{
			try {
				qr.update("DELETE FROM proxies where proxy = ?", 
						myProxy.getProxy());
				
			} catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		else
		{
			String sql = "UPDATE proxies SET is_valided=b'1' WHERE proxy = ?"; 
        	qr.update( sql, myProxy.getProxy()); 
		}
	}
	
	public void deleteValidProxyAfterStart() throws SQLException
	{
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		try {
			qr.update("DELETE FROM proxies where is_valided = b'1'");
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
	}
	
	/**
	 * Create proxy record or update record if record exits to specific table
	 * @param myProxy
	 * @param tableName
	 * @param updateTime
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	private void createOrUpdateSomeProxies(Proxy myProxy, String tableName, Date updateTime) throws SQLException
	{
        try {
//        	conn = DriverManager.getConnection(Constants.URL, Constants.USER, Constants.PASSWORD);
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            Proxy tempProxy = (Proxy) qr.query( 
            		"SELECT * FROM " + tableName + " where proxy = ?", 
            			new BeanHandler(Proxy.class), 
            				myProxy.getProxy()); 
            if (tempProxy == null)
            {
            	String sql = "INSERT INTO " + tableName + " (proxy, last_update) VALUES (?,?)"; 
            	qr.update( sql, myProxy.getProxy(),updateTime); 
            }
            else
            {
            	String sql = "UPDATE " + tableName + " SET last_update=? WHERE proxy = ?"; 
            	qr.update( sql, updateTime,myProxy.getProxy()); 
            }
            
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } 
	}
	
	
	public static void main(String [] args) throws FileNotFoundException, SQLException
	{
		String filePath [] = 
			{"E:/PAUL/WORK/MyWorkspace/workspace/freelancerj2eeworkspace/autoclick/data/MyValid.txt",
				"E:/PAUL/WORK/MyWorkspace/workspace/freelancerj2eeworkspace/autoclick/data/Valid.txt"};
		File []file = {new File(filePath[0]),new File(filePath[1])};
		for(int i = 0; i < 2; i++)
		{
			Scanner scan=new Scanner(file[i]);
			ProxyDao proxyDao = new ProxyDao();
			Proxy proxy = new Proxy();
			ConfigureString.setDriverClassName(Constants.JDBC_DRIVER);
			ConfigureString.setUrl(Constants.URL);
			ConfigureString.setUsername(Constants.USER);
			ConfigureString.setPassword(Constants.PASSWORD);
			
//		System.out.println(proxyDao.getProxyList(1, 10).size());
			
			while(scan.hasNext())
			{
				String proxyStr = scan.nextLine();
				proxy.setProxy(proxyStr);
				proxyDao.createOrUpdateSomeProxies(proxy, "proxies", new Date());
			}
			
		}
	}
}
